using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using UwinEducation.Domain.Entities;
using UwinEducation.Domain.IRepositories;
using UwinEducation.Infrastructure.Persistence;
using UwinEducation.Shared.Enums;
using UwinEducation.Shared.Models;

namespace UwinEducation.Infrastructure.Repositories
{
    /// <summary>
    /// 学生仓储实现
    /// </summary>
    public class StudentRepository : Repository<Student, Guid>, IStudentRepository
    {
        public StudentRepository(ApplicationDbContext context)
            : base(context) { }

        /// <summary>
        /// 获取学生分页列表
        /// </summary>
        public async Task<PagedResult<Student>> GetPagedListAsync(StudentPageRequest pageRequest)
        {
            var query = _context.Students.AsNoTracking().AsQueryable();

            // 如果指定了课程ID，需要通过关联查询筛选学生
            if (pageRequest.CourseId.HasValue)
            {
                // 通过订单详情和教师课表关联查询报名了指定课程的学生
                query = query.Where(s => _context.OrderDetails
                    .Where(od => od.StudentId == s.Id && !od.IsDeleted)
                    .Join(_context.TeacherSchedules.Where(ts => ts.CourseId == pageRequest.CourseId.Value && !ts.IsDeleted),
                        od => od.TeacherScheduleId,
                        ts => ts.Id,
                        (od, ts) => od)
                    .Any());
            }

            // 添加搜索条件
            //if (!string.IsNullOrEmpty(pageRequest.Search))
            //{
            //    query = query.Where(s => s.Name.Contains(pageRequest.Search) ||
            //                           s.StudentNo.Contains(pageRequest.Search));
            //}
            if (!string.IsNullOrEmpty(pageRequest.StudentNo))
            {
                query = query.Where(s => s.StudentNo.Contains(pageRequest.StudentNo));
            }
            if (!string.IsNullOrEmpty(pageRequest.Name))
            {
                query = query.Where(s => s.Name.Contains(pageRequest.Name));
            }
            if (!string.IsNullOrEmpty(pageRequest.School))
            {
                query = query.Where(s => s.School.Contains(pageRequest.School));
            }
            if (pageRequest.Grade.HasValue)
            {
                int year = GetEnrollmentDate(pageRequest.Grade.Value);
                if (year == 1949)
                {
                    query = query.Where(s => s.EnrollmentDate == year);
                }
                else
                {
                    int enrollmentDate = DateTime.Now.Year - year;
                    if (enrollmentDate + 1 > 0 && enrollmentDate + 1 < 13)
                    {
                        query = query.Where(s => s.EnrollmentDate == year);
                    }
                    else if (enrollmentDate + 1 >= 13)
                    {
                        query = query.Where(s =>
                            s.EnrollmentDate <= DateTime.Now.Year - 12 && s.EnrollmentDate != 1949
                        );
                    }
                }
            }
            if (!string.IsNullOrEmpty(pageRequest.Remarks))
            {
                query = query.Where(s => s.Remarks.Contains(pageRequest.Remarks));
            }

            // 去重，因为同一个学生可能报名了同一个课程的多个班次
            query = query.Distinct();

            var total = await query.CountAsync();
            //根据学号排序，学号是string类型比如 0006，转换成int类型
            query = query.OrderBy(s => s.StudentNo.PadLeft(6, '0'));

            var items = await query
                .Skip((pageRequest.PageIndex - 1) * pageRequest.PageSize)
                .Take(pageRequest.PageSize)
                .ToListAsync();

            return new PagedResult<Student> { Total = total, Items = items };
        }

        /// <summary>
        /// 获取年级入学时间
        /// </summary>
        private int GetEnrollmentDate(GradeEnum grade)
        {
            return grade switch
            {
                GradeEnum.Grade1 => DateTime.Now.Year,
                GradeEnum.Grade2 => DateTime.Now.Year - 1,
                GradeEnum.Grade3 => DateTime.Now.Year - 2,
                GradeEnum.Grade4 => DateTime.Now.Year - 3,
                GradeEnum.Grade5 => DateTime.Now.Year - 4,
                GradeEnum.Grade6 => DateTime.Now.Year - 5,
                GradeEnum.Grade7 => DateTime.Now.Year - 6,
                GradeEnum.Grade8 => DateTime.Now.Year - 7,
                GradeEnum.Grade9 => DateTime.Now.Year - 8,
                GradeEnum.Grade10 => DateTime.Now.Year - 9,
                GradeEnum.Grade11 => DateTime.Now.Year - 10,
                GradeEnum.Grade12 => DateTime.Now.Year - 11,
                GradeEnum.Grade13 => DateTime.Now.Year - 12,
                GradeEnum.Grade14 => 1949,
                _ => 0,
            };
        }

        /// <summary>
        /// 获取学生详情
        /// </summary>
        public async Task<Student> GetDetailsByIdAsync(Guid id)
        {
            return await _context.Students.AsNoTracking().FirstOrDefaultAsync(s => s.Id == id);
        }

        /// <summary>
        /// 检查学号是否存在
        /// </summary>
        public async Task<bool> ExistsByStudentNoAsync(string studentNo, Guid? excludeId = null)
        {
            var query = _context.Students.AsNoTracking().AsQueryable();
            if (excludeId.HasValue)
            {
                query = query.Where(s => s.Id != excludeId.Value);
            }
            return await query.AnyAsync(s => s.StudentNo == studentNo);
        }

        /// <summary>
        /// 根据ID列表获取学生列表
        /// </summary>
        public async Task<List<Student>> GetByIdsAsync(List<Guid> guids)
        {
            return await _context
                .Students.AsNoTracking()
                .Where(s => guids.Contains(s.Id))
                .ToListAsync();
        }

        /// <summary>
        /// 根据ID列表获取学生列表
        /// </summary>
        public async Task<List<Student>> GetListByIdsAsync(List<IdRequest> request)
        {
            var ids = request.Select(r => r.Id).ToList();
            var students = await _context
                .Students.AsNoTracking()
                .Where(s => ids.Contains(s.Id))
                .ToListAsync();
            return students;
        }

        /// <summary>
        /// 获取学生编号最大值
        /// </summary>
        public async Task<int> GetMaxStudentNoAsync()
        {
            // 使用原生SQL查询，按数值大小获取最大学号
            var sql =
                @"
                    SELECT CAST(StudentNo AS SIGNED) 
                    FROM basic_students 
                    WHERE IsDeleted = 0 
                    AND StudentNo IS NOT NULL 
                    AND StudentNo != '' 
                    AND StudentNo REGEXP '^[0-9]+$'
                    ORDER BY CAST(StudentNo AS SIGNED) DESC 
                    LIMIT 1";

            var connection = _context.Database.GetDbConnection();

            // 检查连接状态，如果未打开则打开
            if (connection.State != System.Data.ConnectionState.Open)
            {
                await _context.Database.OpenConnectionAsync();
            }

            using var command = connection.CreateCommand();
            command.CommandText = sql;

            var result = await command.ExecuteScalarAsync();
            if (result == null || result == DBNull.Value)
            {
                return 0;
            }

            return Convert.ToInt32(result);
        }
    }
}
